package org.book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.book.util.JDBC_Util;

public class BorrowDaoImp implements BorrowDao {
	private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	@Override
	public List<BorrowBean> getList(int pageCurrent, int pageSize) {
		String sql = "SELECT record_id,rentTime,returnTime,ISBN,bookName,cardNumber,userName\r\n"
				+ "FROM rent_record\r\n" + "INNER JOIN book\r\n" + "ON book.book_id = rent_record.book_id\r\n"
				+ "INNER JOIN `user`\r\n" + "ON user.user_id = rent_record.user_id\r\n" + "limit ?,?";
		return JDBC_Util.getList(sql, (pageCurrent - 1) * pageSize, pageSize);
	}

	@Override
	public int getCount() {
		String sql = "select count(*) from rent_record";
		return JDBC_Util.superSelect(sql);
	}
	
	@Override
	public int getCount(int user_id) {
		String sql = "select count(*) from rent_record where user_id = ?";
		return JDBC_Util.superSelect(sql,user_id);
	}

	@Override
	public boolean remove(int no) {
		String sql = "delete from rent_record where record_id = ?";
		return JDBC_Util.superUpdate(sql, no) != 0;
	}

	@Override
	public boolean isEmpty(int no) {
		String sql = "select count(*) from rent_record where record_id = ?";
		return JDBC_Util.superSelect(sql, no) != 0;
	}

	@Override
	public boolean update(int no, BorrowBean stu) {
		//通过ISBN查询book表中的book_id
		String sql1 = "select book_id from book where ISBN = ?";
		int book_id = JDBC_Util.superSelect(sql1, stu.getISBN());
		//通过cardNunber查询user表中的user_id
		String sql2 = "select user_id from user where cardNumber = ?";
		int user_id = JDBC_Util.superSelect(sql2, stu.getCardNumber());
		
		if(book_id==0||user_id==0) {
			return false;
		}
		//修改
		String sql = "update rent_record set rentTime = ?,returnTime = ?,book_id = ?,user_id = ? where record_id = ?";

		// 字符串转时间
		Date myDate1 = null, myDate2 = null;
		try {
			myDate1 = format.parse(stu.getRentTime());
			myDate2 = format.parse(stu.getReturnTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}

		return JDBC_Util.superUpdate(sql, myDate1, myDate2, book_id, user_id, no) != 0;
	}
	
	@Override
	public boolean update(int id) {
		String sql1 = "select returnTime from rent_record where record_id = ?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String str = null;
		try {
			conn = JDBC_Util.getConnection();
			ps = conn.prepareStatement(sql1);
			ps.setObject(1, id);
			// 获取结果集
			rs = ps.executeQuery();
			if (rs.next()) {
				str = rs.getObject(1).toString();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_Util.closeResource(conn, ps, rs);
		}
		
		// 字符串转时间
		Date myDate1 = null, myDate2 = new Date();
		try {
			myDate1 = format.parse(str);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		int i = myDate1.compareTo(myDate2); 
		if(i==-1) {
			return false;
		}
		
		String sql2 = "update rent_record set returnTime = ? where record_id = ?";
		return JDBC_Util.superUpdate(sql2, myDate2, id)!=0;
		
	}

	@Override
	public boolean add(BorrowBean stu, int book_id, int user_id) {
		String sql = "insert into rent_record(rentTime,returnTime,book_id,user_id) values(?,?,?,?)";

		// 字符串转时间
		Date myDate1 = null, myDate2 = null;
		try {
			myDate1 = format.parse(stu.getRentTime());
			myDate2 = format.parse(stu.getReturnTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return JDBC_Util.superUpdate(sql, myDate1, myDate2,book_id, user_id)!=0;
		
	}

	@Override
	public BorrowBean getStuByNo(int no) {
		String sql = "SELECT record_id,rentTime,returnTime,ISBN,bookName,cardNumber,userName\r\n"
				+ "FROM rent_record\r\n" + "INNER JOIN book\r\n" + "ON book.book_id = rent_record.book_id\r\n"
				+ "INNER JOIN `user`\r\n" + "ON user.user_id = rent_record.user_id\r\n" + "WHERE record_id = ?;";
		return JDBC_Util.getList(sql, no).get(0);
	}

	@Override
	public List<BorrowBean> getLike(String str) {
		String s = '%' + str + '%';
		String sql = "SELECT record_id,rentTime,returnTime,ISBN,bookName,cardNumber,userName\r\n"
				+ "FROM rent_record\r\n" + "INNER JOIN book\r\n" + "ON book.book_id = rent_record.book_id\r\n"
				+ "INNER JOIN `user`\r\n" + "ON user.user_id = rent_record.user_id\r\n" + "WHERE record_id LIKE ?\r\n"
				+ "OR rentTime LIKE ?\r\n" + "OR returnTime LIKE ?\r\n" + "OR ISBN LIKE ?\r\n"
				+ "OR bookName LIKE ?\r\n" + "OR userName LIKE ?\r\n" + "OR cardNumber LIKE ?";
		return JDBC_Util.getList(sql, s, s, s, s, s, s, s);
	}
	@Override
	public List<BorrowBean> getLike(String str, int user_id) {
		String s = '%' + str + '%';
		String sql = "SELECT record_id,ISBN,bookName,price,press,author,rentTime,returnTime\r\n"
				+ "FROM rent_record\r\n" + "INNER JOIN book\r\n" + "ON book.book_id = rent_record.book_id\r\n"
				+ "INNER JOIN `user`\r\n" + "ON rent_record.user_id = ?\r\n" + "WHERE record_id LIKE ?\r\n"
				+ "OR rentTime LIKE ?\r\n" + "OR returnTime LIKE ?\r\n" + "OR ISBN LIKE ?\r\n"
				+ "OR bookName LIKE ?\r\n" + "OR userName LIKE ?\r\n" + "OR cardNumber LIKE ?";
		return JDBC_Util.getList(sql, user_id, s, s, s, s, s, s, s);
	}
	
	//显示每个用户的借阅情况
	//多加一个rent_record_id 不用于显示 用于主动归还需要的id
	@Override
	public List<BorrowBean> getList(int pageCurrent, int pageSize, int user_id) {
		String sql = "SELECT record_id,ISBN,bookName,price,press,author,rentTime,returnTime\r\n" + 
				"FROM rent_record\r\n" + 
				"INNER JOIN book ON book.book_id = rent_record.book_id\r\n" + 
				"WHERE rent_record.user_id = ?\r\n" + 
				"LIMIT ?,?";
		return JDBC_Util.getList(sql, user_id, (pageCurrent - 1) * pageSize, pageSize);
	}

	

	

}
